package com.summit.exam.utils;

import com.summit.exam.entity.QuestionBean;
import com.sun.media.sound.InvalidFormatException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.CellType;

public class Excel07 {
	public static XSSFSheet getFile(String path) throws IOException, InvalidFormatException {
		//创建输入流

		//String path = "（xlsx格式）excel路径";
		FileInputStream fis = new FileInputStream(new File(path));
		//由输入流得到工作簿
		//System.out.println(path);
		//System.out.println(fis);
		XSSFWorkbook workbook = new XSSFWorkbook(fis);
		//得到工作表
		XSSFSheet sheet = workbook.getSheetAt(0);
		

		//得到行,0表示第一行
		XSSFRow row = sheet.getRow(0);
		//创建单元格行号由row确定,列号作为参数传递给createCell;第一列从0开始计算
		XSSFCell cell = row.getCell(0);
		//获取单元格的值
		String cellValue = cell.getStringCellValue();

		System.out.println("单元格的值是：" + cellValue);

		//关闭工作簿
		workbook.close();
		fis.close();

		return sheet;
	}
	
	public ArrayList<QuestionBean> getExcel(String path) {
		ArrayList<QuestionBean> a = new ArrayList<QuestionBean>();
		XSSFSheet file = null;
		try {
			file = getFile(path);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}


		int rowNum = file.getLastRowNum(); //获取sheet的行数

		System.out.println("行数为：" + rowNum);

		for (int i = 1; i <= rowNum; i++) {
			QuestionBean question = new QuestionBean();

			//使用setCellType()方法先将该单元格的类型设置为STRING
			file.getRow(i).getCell(0).setCellType(CellType.STRING);

			question.setQuestionId(Integer.parseInt(file.getRow(i).getCell(0).getStringCellValue()));
			question.setItemCategory(file.getRow(i).getCell(1).getStringCellValue());
			question.setQuestionType(file.getRow(i).getCell(2).getStringCellValue());
			question.setTitle(file.getRow(i).getCell(3).getStringCellValue());

			if(file.getRow(i).getCell(4) != null){
				file.getRow(i).getCell(4).setCellType(CellType.STRING);
			}
			question.setOptionA(file.getRow(i).getCell(4).getStringCellValue());

			if(file.getRow(i).getCell(5) != null){
				file.getRow(i).getCell(5).setCellType(CellType.STRING);
			}
			question.setOptionB(file.getRow(i).getCell(5).getStringCellValue());

			if(file.getRow(i).getCell(6) != null){
				file.getRow(i).getCell(6).setCellType(CellType.STRING);
			}
			question.setOptionC(file.getRow(i).getCell(6).getStringCellValue());

			if(file.getRow(i).getCell(7) != null){
				file.getRow(i).getCell(7).setCellType(CellType.STRING);
			}
			question.setOptionD(file.getRow(i).getCell(7).getStringCellValue());

			question.setAnswer(file.getRow(i).getCell(8).getStringCellValue());

			a.add(question);
		}

		System.out.println(a.toString());

		//question.setQuestionId(getFile(path));
		//question.getTitle() = getFile(path);
		return a;
	}
}
